package com.softer.chengxin.train.dao.provider;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.train.entity.Train;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author Gong Zhiyang
 * @date 2018/10/13
 */
public class TrainDaoProvider {
    /**
     * 查询培训列表数量
     *
     * @param map 查询实体
     * @return 数量
     */
    public String queryTrainNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        Train train = (Train) map.get("train");
        sql.append("SELECT count(*) FROM company_train a LEFT JOIN company_person b ON a.person_id=b.id WHERE a.corp_id=#{train.corpId}");
        commonSql(sql, name, train);
        return sql.toString();
    }

    /**
     * 查询培训列表
     *
     * @param map 查询实体
     * @return 数量
     */
    public String queryTrainList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        Train train = (Train) map.get("train");
        Page page = (Page) map.get("page");
        sql.append("SELECT a.id,b.`name`,a.`subject`,b.duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = b.duties ) AS duties,a.train_begin_time as trainBeginTime ,a.state,a.department AS departmentCode,( SELECT NAME FROM dic_info WHERE CODE = a.department ) AS department");
        sql.append(" FROM company_train a LEFT JOIN company_person b ON a.person_id=b.id WHERE a.corp_id=#{train.corpId}");
        commonSql(sql, name, train);
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    private void commonSql(StringBuffer sql, String name, Train train) {
        if (StringUtils.isNotBlank(name)) {
            sql.append(" AND INSTR(b.`name`,#{name})>0");
        }
        if (StringUtils.isNotBlank(train.getBeginTime())) {
            sql.append(" AND a.train_begin_time>=#{train.beginTime}");
        }
        if (StringUtils.isNotBlank(train.getEndTime())) {
            sql.append(" AND a.train_begin_time<=#{train.endTime}");
        }
        if(StringUtils.isNotBlank(train.getDepartment())){
            sql.append(" AND a.department=#{train.department}");
        }
    }

    /**
     * 查询具体人员培训数量
     *
     * @param map 参数
     * @return 列表
     */
    public String queryPersonTrainNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        sql.append("SELECT count(*) FROM company_train a LEFT JOIN company_person b ON a.person_id=b.id");
        sql.append(" WHERE a.person_id=(SELECT person_id FROM company_train WHERE id=#{id})");
        return sql.toString();
    }
    /**
     * 查询具体人员培训列表
     *
     * @param map 参数
     * @return 列表
     */
    public String queryPersonTrainList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        sql.append("SELECT a.id,b.`name`,a.`subject`,a.department,b.duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = b.duties ) AS duties,a.train_begin_time as trainBeginTime,a.train_end_time as trainEndTime");
        sql.append(" FROM company_train a LEFT JOIN company_person b ON a.person_id=b.id");
        sql.append(" WHERE a.person_id=(SELECT person_id FROM company_train WHERE id=#{id})");
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }
}
